﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows;
using SIO = System.IO;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using Microsoft.Win32;

namespace Excel2SQLBulkTransfer
{
    /// <summary>
    /// Interaction logic for Window1.xaml
    /// </summary>
    public partial class Window1 : Window
    {
        public Window1()
        {
            InitializeComponent();
        }

        DataTable dtXLSData;

        private void btnGet_Click(object sender, RoutedEventArgs e)
        {
            if (string.IsNullOrEmpty(txtImportFilePath.Text) | string.IsNullOrEmpty(cboWorkSheetList.Text))
            {
                MessageBox.Show("Both XLS path and Worksheet must be selected to get the data!");
                return;
            }

            ListView CurListView = new ListView();

            dtXLSData = DB.GetExcel2DataTable(txtImportFilePath.Text, cboWorkSheetList.Text);

            PopLvFromDt(CurListView, dtXLSData);

            gPlaceHolder.Children.Add(CurListView);


            
        }

        private void btnFileBrowse_Click(object sender, RoutedEventArgs e)
        {
            txtImportFilePath.Text = "";
            cboWorkSheetList.ItemsSource = null;

            OpenFileDialog ofdMST = new OpenFileDialog();
            ofdMST.Multiselect = false;
            ofdMST.Filter = "XLS Files|*.xls";

            ofdMST.ShowDialog();
            txtImportFilePath.Text = ofdMST.FileName;

            if (string.IsNullOrEmpty(txtImportFilePath.Text)) return;

            if (!SIO.File.Exists(txtImportFilePath.Text))
            {
                MessageBox.Show("File Does Not Exist");
                return;
            }

            List<string> CurrentWorkSheet = DB.GetExcelSheetNames(txtImportFilePath.Text);

            cboWorkSheetList.ItemsSource = CurrentWorkSheet;
        }

        private void PopLvFromDt( ListView inLv, DataTable inDt)
        {
            GridView gwResults = new GridView();

            foreach (DataColumn CurCol in inDt.Columns)
            {
                GridViewColumn newGVC = new GridViewColumn();
                newGVC.DisplayMemberBinding = new Binding(CurCol.ColumnName);
                newGVC.Header = CurCol.ColumnName;
                gwResults.Columns.Add(newGVC);
            }

            inLv.View = gwResults;
            inLv.ItemsSource = inDt.DefaultView;

        }

        private void txtDBServer_TextChanged(object sender, TextChangedEventArgs e)
        {

        }

        private void Button_Click(object sender, RoutedEventArgs e)
        {
            if(dtXLSData != null)
            {
                if (string.IsNullOrEmpty(txtDBServer.Text) |
                    string.IsNullOrEmpty(cboDBDataBase.Text) |
                        string.IsNullOrEmpty(cboDBDtable.Text))
                {
                    MessageBox.Show("Invalid Database Info!");
                    return;
                }

                DB.BulkImport2SQL(txtDBServer.Text, cboDBDataBase.Text, cboDBDtable.Text, dtXLSData);
            }
        }

        private void cboDBDataBase_GotFocus(object sender, RoutedEventArgs e)
        {
            if (!string.IsNullOrEmpty(txtDBServer.Text))
            {
                List<string> DataBaseNames = DB.GetDataBaseNames(txtDBServer.Text);

                cboDBDataBase.ItemsSource = DataBaseNames;
            }
        }

        private void cboDBDtable_GotFocus(object sender, RoutedEventArgs e)
        {
            if (!string.IsNullOrEmpty(cboDBDataBase.Text) & !string.IsNullOrEmpty(txtDBServer.Text))
            {
                List<string> tableNames = DB.GetDataBaseTables(txtDBServer.Text, cboDBDataBase.Text);

                cboDBDtable.ItemsSource = tableNames;
            }
        }

        private void Button_Click_1(object sender, RoutedEventArgs e)
        {

        }

        private void btnGuessSchema_Click(object sender, RoutedEventArgs e)
        {
            if (dtXLSData == null)
            {
                return;
            }
            else if(dtXLSData.Rows.Count == 0)
            {
                return;
            }
            

            StringBuilder sbCreateTable = new StringBuilder();
                            sbCreateTable.AppendLine("SET ANSI_NULLS ON");
                            sbCreateTable.AppendLine(" GO");
                            sbCreateTable.AppendLine(" SET QUOTED_IDENTIFIER ON");
                            sbCreateTable.AppendLine(" GO");
                            sbCreateTable.AppendLine(" CREATE TABLE <ENTER TABLE NAME > (");
            
                    for(int i = 0; i < dtXLSData.Columns.Count; i++)
                    {
                        string sColumnName = dtXLSData.Columns[i].ColumnName;
                        string sDataType = "";
                        int iLength = 0;

                        foreach(DataRow CurRow in dtXLSData.Rows)
                        {
                           int iCurLength = CurRow[sColumnName].ToString().Length;
                           
                           if(iCurLength > iLength)
                           {
                                iLength = iCurLength;
                           }
                        }

                        if (iLength <= 50)
                        {
                            sDataType = "[nvarchar](50)";
                        }
                        else
                        {
                            if (iLength <= 255)
                            {
                                sDataType = "[nvarchar](255)";
                            }
                            else
                            {
                                sDataType = "[TEXT]";
                            }
                        }

                        string sLine = string.Format(" [{0}] {1} NULL", sColumnName, sDataType);

                        if (i == (dtXLSData.Columns.Count - 1))
                        {
                            sbCreateTable.AppendLine(sLine);
                        }
                        else
                        {
                            sbCreateTable.AppendLine(sLine + ",");
                        }
                    }

                    sbCreateTable.AppendLine(" ) ON [PRIMARY]");
                    sbCreateTable.AppendLine("GO");

                    ModalDialog mdShowGuessSchema = new ModalDialog();
                    mdShowGuessSchema.MessageTextBox.Text = sbCreateTable.ToString();
                    mdShowGuessSchema.Show();
                    
                    

        }
        
    }
}
